package com.info.zhiduoduo.repository.mapper.taxer;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.account.MonthZhiduoTradingVolumeDto;
import com.info.zhiduoduo.repository.dto.invoice.InvoiceAmountDto;
import com.info.zhiduoduo.repository.dto.project.ProjectContractDto;
import com.info.zhiduoduo.repository.dto.statistics.StatisticLastThreeMonthsDto;
import com.info.zhiduoduo.repository.dto.taxer.TaxerInvoiceDto;
import com.info.zhiduoduo.repository.dto.taxer.TaxerInvoicesDto;
import com.info.zhiduoduo.repository.entity.taxer.TaxerInvoice;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *  发票列表 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-11-17
 */
public interface TaxerInvoiceMapper extends BaseMapper<TaxerInvoice> {

	@Select(" select ti.*,tip.projectname,tip.categoryid,tip.categoryname,tip.projectcycle,tip.projectintro," +
			" tp.state as paidState " +
			" from zdd_taxer_invoice ti " +
			" left join zdd_taxer_invoice_project tip on tip.invoiceid = ti.invoiceid " +
			" left join zdd_taxer_payment tp on tp.payid = ti.payid " +
			" where ti.userid = #{userid} " +
			" and ti.deleted = 0")
	List<TaxerInvoiceDto> getInvoiceListByUserid(int userid);

	@Select(" select ti.*," +
//            " tip.projectname,tip.categoryid,tip.categoryname,tip.projectcycle,tip.projectintro," +
			" tp.state as paidState " +
			" from zdd_taxer_invoice ti " +
			" left join zdd_taxer_invoice_project tip on tip.projectid = ti.projectid " +
			" left join zdd_taxer_payment tp on tp.payid = ti.payid " +
			" where ti.invoiceid = #{invoiceid}")
	TaxerInvoiceDto getInvoice(int invoiceid);

	@Select(" select ti.*,tc.name as taxCityName,ma.temporary_login,ma.opening_location,mop.name as openingLocationName," +
			" sm.managername,tp.state as paidState,ms.tax_acct_state,m.maker_type,m.phone,ma.realname,ms.studiolicensephoto," +
			" ms.addr,ub.bankname,ub.open_acct_bank_name,ub.acctno " +
			" from zdd_taxer_invoice ti " +
			" left join zdd_taxer_invoice_project tip on tip.projectid = ti.projectid " +
			" left join zdd_system_manager zsm on zsm.managerid = ti.supplement_personnel " +
			" left join zdd_system_manager sm on sm.managerid = ti.managerid " +
			" left join zdd_taxer_payment tp on tp.payid = ti.payid " +
			" join zdd_maker_studio ms on ms.userid = ti.userid " +
			" left join zdd_maker_account ma on ma.userid = ti.userid " +
			" join zdd_maker m on m.userid = ti.userid " +
			" left join zdd_maker_bankcard ub on ub.userid = ma.userid and ub.state = 1 and ub.accttype = 1" +
			" left join zdd_city_config tc on tc.id = ti.tax_city " +
			" left join zdd_city_config mop on mop.id = ma.opening_location " +
			" where CONCAT(ti.username,ti.company_name,ti.applyno,ma.realname) like '%${name}%' " +
			" and if(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" and if(#{paidState}=0, 1=1, tp.state = #{paidState}) " +
			" and if(#{state}=0, 1=1, ti.state = #{state}) " +
			" and if(#{invoiceType}=0, 1=1, ti.invoice_type = #{invoiceType}) " +
			" and ti.createtime between #{startDate} and #{endDate} " +
			" and ti.deleted = 0" +
			" ${dockingCity} " +
			" order by ti.invoiceid desc ")
	List<TaxerInvoiceDto> getInvoiceList(int userid, String name, Integer paidState, int state, Integer invoiceType, Date startDate,
	                                     Date endDate,String dockingCity);


	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where ti.userid = #{userid} " +
			" and ti.invoiceid != #{invoiceid} " +
			" and YEAR(tp.confirmtime) = YEAR(NOW()) " +
			" and ti.deleted = 0")
	BigDecimal getYearlyTotalAmount(@Param("userid") Integer userid, @Param("invoiceid") Integer invoiceid);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where ti.userid = #{userid} " +
			" AND ti.invoiceid != #{invoiceid} " +
			" and YEAR(tp.confirmtime) = YEAR(NOW()) " +
			" AND QUARTER(tp.confirmtime) = QUARTER(NOW()) " +
			" and ti.deleted = 0")
	BigDecimal getQuarterTotalAmount(@Param("userid") int userid, @Param("invoiceid") int invoiceid);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where ti.userid = #{userid} " +
			" and ti.invoiceid != #{invoiceid} " +
			" and YEAR(tp.confirmtime) = YEAR(NOW()) " +
			" and ti.deleted = 0")
	BigDecimal getYearlyTotalAmountPaid(@Param("userid") Integer userid, @Param("invoiceid") Integer invoiceid);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid})  " +
			" and ti.invoiceid != #{invoiceid} " +
			" and tp.confirmtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH) " +
			" and ti.deleted = 0")
	BigDecimal getTwelveMonthTotalAmountPaid(@Param("userid") Integer userid, int invoiceid);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" AND ti.invoiceid != #{invoiceid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and IF(#{quarter}=0,QUARTER(tp.confirmtime) = QUARTER(NOW()),QUARTER(tp.confirmtime) = #{quarter})  " +
			" and ti.deleted = 0")
	BigDecimal getQuarterTotalAmountPaid(int userid, int invoiceid, int year, int quarter);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" AND ti.invoiceid != #{invoiceid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and IF(#{month}=0, MONTH(tp.confirmtime) = MONTH(NOW()),MONTH(tp.confirmtime) = #{month}) " +
			" and ti.deleted = 0")
	BigDecimal getMonthTotalAmount(@Param("userid") int userid, @Param("invoiceid") int invoiceid, int year, int month);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" join zdd_maker_studio ms on ms.userid = ti.userid and taxer = 1" +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" and ti.invoiceid != #{invoiceid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and IF(#{month}=0, MONTH(tp.confirmtime) = MONTH(NOW()),MONTH(tp.confirmtime) = #{month}) " +
			" and ti.deleted = 0")
	BigDecimal getMonthAgentTaxerTotalAmount(@Param("userid") int userid, @Param("invoiceid") int invoiceid, int year, int month);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" join zdd_maker_studio ms on ms.userid = ti.userid and taxer = 1" +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" and ti.invoiceid != #{invoiceid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and ti.deleted = 0")
	BigDecimal getYearAgentTaxerTotalAmount(@Param("userid") int userid, @Param("invoiceid") int invoiceid, int year);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" join zdd_maker m on m.userid = ti.userid and maker_type = 2 " +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" and ti.invoiceid != #{invoiceid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and IF(#{month}=0, MONTH(tp.confirmtime) = MONTH(NOW()),MONTH(tp.confirmtime) = #{month}) " +
			" and ti.deleted = 0")
	BigDecimal getMonthTaxerTotalAmount(@Param("userid") int userid, @Param("invoiceid") int invoiceid, int year, int month);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" join zdd_maker m on m.userid = ti.userid and maker_type = 2 " +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" and ti.invoiceid != #{invoiceid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and ti.deleted = 0")
	BigDecimal getYearTaxerTotalAmount(@Param("userid") int userid, @Param("invoiceid") int invoiceid, int year);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where IF(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" AND ti.invoiceid != #{invoiceid} " +
			" and ti.invoice_type = 1 " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and IF(#{month}=0, MONTH(tp.confirmtime) = MONTH(NOW()),MONTH(tp.confirmtime) = #{month}) " +
			" and ti.deleted = 0")
	BigDecimal getMonthGenTotalAmount(@Param("userid") int userid, @Param("invoiceid") int invoiceid, int year, int month);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where ti.userid = #{userid} " +
			" AND ti.invoice_type = 1 " +
			" AND ti.invoiceid != #{invoiceid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and IF(#{quarter}=0,QUARTER(tp.confirmtime) = QUARTER(NOW()),QUARTER(tp.confirmtime) = #{quarter}) " +
			" AND ti.deleted = 0 ")
	BigDecimal getQuarterGenProjectTotalAmountPaid(int userid, int invoiceid, int year, int quarter);

	@Select(" select IFNULL(SUM(ti.tax_fee),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where ti.userid = #{userid} " +
			" and ti.invoiceid != #{invoiceid} " +
			" and YEAR(tp.confirmtime) = YEAR(NOW()) " +
			" and ti.deleted = 0")
	BigDecimal getTaxerYearlyTaxFee(int userid, int invoiceid);

	@Select(" select IFNULL(SUM(ti.total_charge),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" where ti.userid = #{userid} " +
			" and IF(#{year}=0, YEAR(tp.confirmtime) = YEAR(NOW()), YEAR(tp.confirmtime) = #{year}) " +
			" and IF(#{quarter}=0,QUARTER(tp.confirmtime) = QUARTER(NOW()),QUARTER(tp.confirmtime) = #{quarter})")
	BigDecimal getUserQuarterServiceCharge(int userid, int year, int quarter);

	@Select(" select * from zdd_taxer_invoice " +
			" where IF(#{userid} = 0, 1=1, userid = #{userid}) " +
			" and state = 1 " +
			" and deleted = 0 ")
	List<TaxerInvoice> getUnfinishList(@Param("userid") Integer userid);

	/**
	 * 该创客目前参与的 未开票的智杰祱项目个数
	 * @param userid 创客userid
	 * @return int
	 */
	@Select(" SELECT count( * )  FROM zdd_taxer_invoice " +
			" WHERE deleted = 0  AND userid = #{userid} AND state != 2")
	int notTaxerProjectInvoicedCountByUserid(@Param("userid") int userid);

	@Select(" SELECT " +
			" DATE_FORMAT( tp.confirmtime, '%Y' ) AS years," +
			" DATE_FORMAT( tp.confirmtime, '%m' ) AS months, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN ti.amount END ), 0 )  AS tianJinProjectamount, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN ti.service_charge END ), 0 ) AS tianJinProjectservicecharge, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN ti.tax_fee END ), 0 ) AS tianJinProjecttaxfee, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN tp.amount END ), 0 ) AS tianJinAllTaxFee, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN ti.amount END ), 0 ) AS zhouShanProjectamount, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN ti.service_charge END ), 0 ) AS zhouShanProjectservicecharge, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN ti.tax_fee END ), 0 ) AS zhouShanProjecttaxfee, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN tp.amount END ), 0 ) AS zhouShanAllTaxFee " +
			" FROM zdd_taxer_invoice ti " +
			" LEFT JOIN zdd_taxer_payment tp ON tp.invoiceid = ti.invoiceid " +
			" LEFT JOIN zdd_maker m ON m.userid = ti.userid" +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = ti.userid " +
			" WHERE " +
			" ma.opening_location IN ( 1, 4 ) " +
			" AND tp.state = 2 " +
			" AND tp.type = 3 " +
			" AND m.maker_type in(1, 2) " +
			" AND YEAR ( tp.confirmtime ) = #{years}  " +
			" GROUP BY months ")
	List<MonthZhiduoTradingVolumeDto> getTaxerTradingVolumeList(int years);

	@Select(" SELECT " +
			" DATE_FORMAT( tp.confirmtime, '%Y' ) AS years," +
			" DATE_FORMAT( tp.confirmtime, '%m' ) AS months, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN ti.amount END ), 0 )  AS tianJinProjectamount, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN ti.service_charge END ), 0 ) AS tianJinProjectservicecharge, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN ti.tax_fee END ), 0 ) AS tianJinProjecttaxfee, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 1 THEN tp.amount END ), 0 ) AS tianJinAllTaxFee, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN ti.amount END ), 0 ) AS zhouShanProjectamount, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN ti.service_charge END ), 0 ) AS zhouShanProjectservicecharge, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN ti.tax_fee END ), 0 ) AS zhouShanProjecttaxfee, " +
			" IFNULL( sum( CASE ma.opening_location WHEN 4 THEN tp.amount END ), 0 ) AS zhouShanAllTaxFee " +
			" FROM zdd_taxer_invoice ti " +
			" LEFT JOIN zdd_taxer_payment tp ON tp.invoiceid = ti.invoiceid " +
			" LEFT JOIN zdd_maker m ON m.userid = ti.userid" +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = ti.userid " +
			" WHERE " +
			" ma.opening_location IN ( 1, 4 ) " +
			" AND tp.state = 2 " +
			" AND tp.type = 3 " +
			" AND m.maker_type = 1 " +
			" AND YEAR ( tp.confirmtime ) = #{years}  " +
			" GROUP BY months ")
	List<MonthZhiduoTradingVolumeDto> getPureAgencyTradingVolumeList(int years);

	@Delete(" delete from zdd_taxer_invoice " +
			" where userid = #{userid} " +
			" and YEAR(createtime) = YEAR(NOW()) " +
			" and QUARTER(createtime) = QUARTER(#{quarter})")
	int deleteQuarterData(@Param("userid") int userid, @Param("quarter") int quarter);

	@Delete(" delete from zdd_taxer_invoice " +
			" where userid = #{userid} " +
			" and YEAR(createtime) = YEAR(NOW()) ")
	int deleteYearData(@Param("userid") int userid);



@Select("select  sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=1,inv.amount,0)) as january,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=2,inv.amount,0)) as february,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=3,inv.amount,0)) as march,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=4,inv.amount,0)) as april,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=5,inv.amount,0)) as may,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=6,inv.amount,0)) as june,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=7,inv.amount,0)) as july,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=8,inv.amount,0)) as august,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=9,inv.amount,0)) as september,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=10,inv.amount,0)) as october,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=11,inv.amount,0)) as november,\n" +
		"sum(if(year(inv.confirmtime)=#{year} and month(inv.confirmtime)=12,inv.amount,0)) as december \n" +
		"from zdd_taxer_invoice inv\n" +
		"left join zdd_taxer_invoice_project pro on inv.projectid=pro.projectid\n" +
		"left join zdd_maker mak on inv.userid=mak.userid\n" +
		"where mak.maker_type=#{maker_type} and  inv.state=2")
	List<InvoiceAmountDto> getInvoiceTaxerAmount(@Param("year") Integer year,@Param("maker_type") Integer maker_type);

	/**
	 * 查询智众包企业N个月内的交易时段和交易额
	 * @param taxerName 工作室名
	 * @param n n个月
	 * @return
	 */
	@Select("SELECT username as name,MIN(DAY(confirmtime)) start,MAX(DAY(confirmtime)) end,SUM(amount) gmv " +
			"FROM zdd_taxer_invoice " +
			"WHERE state = 2 AND deleted = 0 " +
			"AND DATE(confirmtime) BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL #{n} MONTH),interval -day(curdate())+1 day) AND LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) " +
			"AND username LIKE CONCAT(\"%\",IFNULL(#{taxerName},\"\"),\"%\") " +
			"GROUP BY userid")
    List<StatisticLastThreeMonthsDto> getTaxerStatisticLastNMonths(@Param("taxerName") String taxerName, @Param("n") Integer n);

	@Select("select invoiceid,i.projectid,i.companyid as compid,i.company_name as compname,i.userid,i.username," +
			" round(i.amount,2) as serviceCharge,i.projectname,i.projectintro,'智圣（天津）网络科技有限公司' as partyName," +
			" i.createtime as contractDate," +
			" CONCAT('JSX',DATE_FORMAT(i.createtime,'%Y%m%d'),invoiceid) as contractNo" +
			" from zdd_taxer_invoice i " +
			" where i.invoiceid = #{invoiceId}")
	ProjectContractDto getProjectContractInfo(Integer invoiceId);

	@Select(" select ti.*,tc.name as taxCityName,ma.temporary_login,ma.opening_location,mop.name as openingLocationName," +
//            " tip.projectname,tip.categoryid,tip.categoryname,tip.projectcycle,tip.projectintro," +
			" sm.managername,tp.state as paidState,ms.tax_acct_state,m.maker_type,m.phone,ma.realname,ms.studiolicensephoto,ms.addr " +
			" from zdd_taxer_invoice ti " +
			" left join zdd_taxer_invoice_project tip on tip.projectid = ti.projectid " +
			" left join zdd_system_manager zsm on zsm.managerid = ti.supplement_personnel " +
			" left join zdd_system_manager sm on sm.managerid = ti.managerid " +
			" left join zdd_taxer_payment tp on tp.payid = ti.payid " +
			" join zdd_maker_studio ms on ms.userid = ti.userid " +
			" left join zdd_maker_account ma on ma.userid = ti.userid " +
			" join zdd_maker m on m.userid = ti.userid " +
			" left join zdd_city_config tc on tc.id = ti.tax_city " +
			" left join zdd_city_config mop on mop.id = ma.opening_location " +
			" where CONCAT(ti.username,ti.company_name,ti.applyno) like '%${name}%' " +
			" and if(#{userid}=0, 1=1, ti.userid = #{userid}) " +
			" and if(#{paidState}=0, 1=1, tp.state = #{paidState}) " +
			" and if(#{state}=0, 1=1, ti.state = #{state}) " +
			" and if(#{invoiceType}=0, 1=1, ti.invoice_type = #{invoiceType}) " +
			" and ti.createtime between #{startDate} and #{endDate} " +
			" and ti.deleted = 0" +
			" ${dockingCity} " +
			" order by ti.invoiceid desc ")
	List<TaxerInvoicesDto> getInvoiceLists(int userid, String name, Integer paidState, int state, Integer invoiceType, Date startDate,
										   Date endDate, String dockingCity);

	@Select(" select IFNULL(SUM(ti.amount),0) " +
			" from zdd_taxer_invoice ti " +
			" join zdd_taxer_payment tp on tp.invoiceid = ti.invoiceid and tp.state = 2 " +
			" join zdd_maker_studio ms on ms.userid = ti.userid and taxer = 1" +
			" where ti.userid in (${userIds}) " +
			" and ti.invoiceid != #{invoiceid} " +
			" and IF(#{timeType} = 1 ,YEAR(tp.confirmtime) = YEAR(NOW()), " +
			"     tp.confirmtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH))" +
			" and ti.deleted = 0")
	BigDecimal getTaxerAmountByUserIds(@Param("userIds") String userIds, @Param("invoiceid") int invoiceid,@Param("timeType") int timeType);

	/**
	 * timeType : 时间类型：1：当前月度，2：当前季度，3：当前年度，4：当前时间往前的12个月
	 * @param userId：用户id
	 * @param timeType
	 * @return
	 */
	@Select("select IFNULL(SUM(i.amount),0) " +
			" from zdd_taxer_invoice i " +
			" where i.state = 2 " +
			" and i.userid = #{userId} and i.deleted = 0 " +
			" and " +
			"   CASE " +
			"   when #{timeType} = 1 then YEAR(i.confirmtime) = YEAR(NOW()) and MONTH(i.confirmtime) = MONTH(NOW()) " +
			"   when #{timeType} = 2 then YEAR(i.confirmtime) = YEAR(NOW()) and QUARTER(i.confirmtime) = QUARTER(NOW())" +
			"   when #{timeType} = 3 then YEAR(i.confirmtime) = YEAR(NOW()) " +
			"   when #{timeType} = 4 then i.confirmtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH)" +
			"   END")
	BigDecimal getUserCompleteAmount(Integer userId, Integer timeType);
}
